﻿//======================================================
//==     (c)2008 aspxcms inc by NeTCMS v1.0              ==
//==          Forum:bbs.aspxcms.com                   ==
//==         Website:www.aspxcms.com                  ==
//======================================================
using System;
using System.Data;
using System.Data.SqlClient;
using NetCMS.DALFactory;
using NetCMS.Model;
using System.Text.RegularExpressions;
using System.Text;
using System.Reflection;
using NetCMS.DALProfile;
using NetCMS.Config;

namespace NetCMS.DALSQLServer
{
    public class DefineTable : DbBase, IDefineTable
    {
        #region DefineTable.aspx
        public DataTable Sel_DefineInfoId()
        {
            string Sql = "Select DefineInfoId,DefineName,ParentInfoId From " + Pre + "define_class where SiteID='" + NetCMS.Global.Current.SiteID + "'";
            return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
        }
        public DataTable Sel_ParentInfo(string PID, int flag)
        {
            #region
            SqlParameter param = new SqlParameter("@ParentInfoId", PID);
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Select DefineInfoId,DefineName,ParentInfoId From " + Pre + "define_class where ParentInfoId=@ParentInfoId and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "Select DefineID,DefineInfoId,DefineName,ParentInfoID From " + Pre + "define_class where ParentInfoID=@ParentInfoID";
            }
            else if (flag == 2)
            {
                Sql = "Select DefineInfoId,DefineName,ParentInfoId From " + Pre + "define_class where DefineInfoId=@ParentInfoID";
            }
            return DbHelper.ExecuteTable(CommandType.Text, Sql, param);
            #endregion
        }
        public int sel_defCname(string defCname)
        {
            SqlParameter param = new SqlParameter("@DefineCname", defCname);
            string Sql = "Select count(id) From " + Pre + "Define_Data  Where DefineCname=@DefineCname";
            return (int)DbHelper.ExecuteScalar(CommandType.Text, Sql, param);
        }
        public int sel_defEname(string defEname)
        {
            SqlParameter param = new SqlParameter("@defineColumns", defEname);
            string Sql = "Select count(id) From " + Pre + "Define_Data  Where defineColumns=@defineColumns";
            return (int)DbHelper.ExecuteScalar(CommandType.Text, Sql, param);
        }

        public int Add(string Str_ColumnsType, string defCname, string defEname, int definSelected, int Isnull, string defColumns, string defExp, string definedvalue,int DtType)
        {
            #region
            SqlParameter[] param = new SqlParameter[9];
            param[0] = new SqlParameter("@ColumnsType", SqlDbType.NVarChar, 12);
            param[0].Value = Str_ColumnsType;
            param[1] = new SqlParameter("@defCname", SqlDbType.NVarChar, 50);
            param[1].Value = defCname;
            if (defEname == null)
                defEname = "";
            param[2] = new SqlParameter("@defEname", SqlDbType.NVarChar, 50);
            param[2].Value = defEname;
            param[3] = new SqlParameter("@defineType", SqlDbType.Int, 4);
            param[3].Value = definSelected;
            param[4] = new SqlParameter("@Is_null", SqlDbType.Int, 4);
            param[4].Value = Isnull;
            if (defColumns == null)
                defColumns = "";
            param[5] = new SqlParameter("@defColumns", SqlDbType.NText);
            param[5].Value = defColumns;
            if (defExp == null)
                defExp = "";
            param[6] = new SqlParameter("@defExp", SqlDbType.NVarChar, 200);
            param[6].Value = defExp;
            if (definedvalue == null)
                definedvalue = "";
            param[7] = new SqlParameter("@definedvalue", SqlDbType.NVarChar, 200);
            param[7].Value = definedvalue;
            param[8] = new SqlParameter("@DtType", SqlDbType.NVarChar, 200);
            param[8].Value = DtType;

            string Sql = "Insert Into " + Pre + "Define_Data(defineInfoId,DefineCname,DefineColumns,defineType,IsNull,defineValue," +
                         "defineExpr,SiteID,definedvalue,Type) Values(@ColumnsType,@defCname,@defEname,@defineType,@Is_null,@defColumns," +
                         "@defExp,'" + NetCMS.Global.Current.SiteID + "',@definedvalue,@DtType)";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }
        #endregion

        #region DefineTable_Edit_List.aspx
        public DataTable Str_Start_Sql(int ID)
        {
            SqlParameter param = new SqlParameter("@ID", ID);
            string Sql = "Select id,defineInfoId,defineCname,defineColumns,defineType,IsNull,defineValue,defineExpr,definedvalue,Type From " + Pre + "define_data where Id=@ID";
            return DbHelper.ExecuteTable(CommandType.Text, Sql, param);
        }
        public int Update(string Str_ColumnsType, string Str_DefName, string Str_DefEname, string Str_DefType, int Str_DefIsNull, string Str_DefColumns, string Str_DefExpr, int DefID, string definedvalue,int type)
        {
            #region
            SqlParameter[] param = new SqlParameter[10];
            param[0] = new SqlParameter("@ColumnsType", SqlDbType.NVarChar, 12);
            param[0].Value = Str_ColumnsType;
            param[1] = new SqlParameter("@defCname", SqlDbType.NVarChar, 50);
            param[1].Value = Str_DefName;
            if (Str_DefEname == null)
                Str_DefEname = "";
            param[2] = new SqlParameter("@defEname", SqlDbType.NVarChar, 50);
            param[2].Value = Str_DefEname;
            param[3] = new SqlParameter("@defineType", SqlDbType.Int, 4);
            param[3].Value = Convert.ToInt32(Str_DefType);
            param[4] = new SqlParameter("@Is_null", SqlDbType.Int, 4);
            param[4].Value = Convert.ToInt32(Str_DefIsNull);
            param[5] = new SqlParameter("@defColumns", SqlDbType.NText);
            if (Str_DefColumns == null)
                Str_DefColumns = "";
            param[5].Value = Str_DefColumns;
            param[6] = new SqlParameter("@defExp", SqlDbType.NVarChar, 200);
            if (Str_DefExpr == null)
                Str_DefExpr = "";
            param[6].Value = Str_DefExpr;
            param[7] = new SqlParameter("@definedvalue", SqlDbType.NVarChar, 200);
            if (definedvalue == null)
                definedvalue = "";
            param[7].Value = definedvalue;
            param[8] = new SqlParameter("@DefID", SqlDbType.Int, 4);
            param[8].Value = DefID;
            param[9] = new SqlParameter("@type", SqlDbType.Int, 4);
            param[9].Value = type;

            string Sql = "Update " + Pre + "define_data Set defineInfoId=@ColumnsType,defineCname=@defCname," +
                         "defineColumns=@defEname,defineType=@defineType,IsNull=@Is_null,defineValue=@defColumns," +
                         "defineExpr=@defExp,definedvalue=@definedvalue,Type=@type where id=@DefID";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }
        #endregion

        #region DefineTable_Edit_Manage.aspx
        public int update_defineClass(string Str_NewText, string DefID)
        {
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@DefineName", Str_NewText), new SqlParameter("@DefineInfoId", DefID) };
            string Sql = "Update " + Pre + "define_class Set DefineName=@DefineName where DefineInfoId=@DefineInfoId";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
        }
        #endregion

        #region DefineTable_List.aspx

        public DataTable GetPage(string defid, int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            #region
            string where = "";
            if (defid == null && defid == string.Empty)
            {
                where = "";
            }
            else
            {
                where = "  where defineInfoId=@defineInfoId";
            }
            SqlParameter param = new SqlParameter("@defineInfoId", defid);
            string AllFields = "id,defineInfoId,defineCname,defineType,[IsNull]";
            string Condition = "" + Pre + "Define_Data " + where + "";
            string IndexField = "id";
            string OrderFields = "order by id Desc";
            return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, param);
            #endregion
        }
        public int del_defineStr(string pr)
        {
            SqlParameter param = new SqlParameter("@defineInfoId", pr);
            string Sql= "Delete From " + Pre + "define_data where defineInfoId=@defineInfoId";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
        }
        public int del_deData(int DefID)
        {
            string Sql = "Delete From " + Pre + "define_data where Id=" + DefID + "";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);
        }
        #endregion

        public int sel_defineClass(string _NewText,int flag)
        {
            #region
            SqlParameter param = new SqlParameter("@DefineName", _NewText);
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Select count(DefineId) From " + Pre + "Define_Class Where DefineName=@DefineName";
            }
            else if (flag == 1)
            {
                Sql = "Select count(DefineId) From " + Pre + "Define_Class Where DefineInfoId=@DefineName";
            }
            return (int)DbHelper.ExecuteScalar(CommandType.Text, Sql, param);
            #endregion
        }
        public int add_defineClass(string rand, string _NewText, string _PraText)
        {
            SqlParameter[] param = new SqlParameter[] { new SqlParameter("@DefineInfoId", rand), new SqlParameter("@DefineName", _NewText), new SqlParameter("@ParentInfoId", _PraText) };
            string Sql = "Insert Into " + Pre + "Define_Class(DefineInfoId,DefineName,ParentInfoId,SiteID) values(@DefineInfoId,@DefineName,@ParentInfoId,'" + NetCMS.Global.Current.SiteID + "')";
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
        }
        public void del_defineClass(string DefID, int flag)
        {
            #region
            SqlParameter param = null;
            if (flag < 3)
            {
                param = new SqlParameter("@DefineInfoId", DefID);
            }
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Delete From " + Pre + "define_class where DefineInfoId=@DefineInfoId";
            }
            else if (flag == 1)
            {
                Sql = "Delete From " + Pre + "define_class where ParentInfoId=@DefineInfoId";
            }
            else if (flag == 2)
            {
                Sql = "Delete From " + Pre + "define_data where defineInfoId=@DefineInfoId";
            }
            else if (flag == 3)
            {
                Sql = "Delete From " + Pre + "define_class where DefineInfoId in(" + DefID + ")";
            }
            else if (flag == 4)
            {
                Sql = "Delete From " + Pre + "define_class where ParentInfoId in(" + DefID + ")";
            }
            else if (flag == 5)
            {
                Sql = "Delete From " + Pre + "define_data where Id in(" + DefID + ")";
            }
            DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            #endregion
        }
        public int del_defClassInfo(int flag)
        {
            #region
            string Sql = "";
            if (flag == 0)
            {
                if (NetCMS.Global.Current.SiteID == "0") 
                { 
                    Sql = "Delete From " + Pre + "define_class"; 
                }
                else 
                { 
                    Sql = "Delete From " + Pre + "define_class where SiteID='" + NetCMS.Global.Current.SiteID + "'"; 
                }
            }
            else if (flag == 1)
            {
                if (NetCMS.Global.Current.SiteID == "0")
                { 
                    Sql = "Delete From " + Pre + "define_data"; 
                }
                else 
                { 
                    Sql = "Delete From " + Pre + "define_data where SiteID='" + NetCMS.Global.Current.SiteID + "'"; 
                }
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);
            #endregion
        }
        //读取属于自定义字段
        public DataTable getDefineUser(int type)
        {
            string sql = "select id,defineInfoId,defineCname,defineColumns,defineType,IsNull,defineValue,defineExpr,definedvalue,SiteID,Type from " + Pre + "define_data where type=" + type;
            return DbHelper.ExecuteTable(CommandType.Text,sql,null);
        }
        
        //根据会员编号和自定义字段类型读取自定义字段
        public DataTable setDefineByNum(string userNum)
        {
            string sql = "select a.*,deID=b.Id,b.DsnewsId,b.DsEName,b.DsnewsTable,b.DsType,b.DsContent,b.DsApiId,b.SiteID from " + Pre + "define_data a inner join " + Pre + "define_save b on a.defineColumns=b.DsEname where b.DsnewsId='" + userNum + "'";
            return DbHelper.ExecuteTable(CommandType.Text, sql, null);
        }

        //添加修改会员自定义字段
        /// <summary>
        /// </summary>
        /// <param name="DsnewsId">ID编号</param>
        /// <param name="DsEName">英文名称</param>
        /// <param name="DsnewsTable">保存会员信息表名字</param>
        /// <param name="DsType">自定义类型  系统默认后台数据为:0,前台为1 </param>
        /// <param name="DsContent">内容</param>
        /// <param name="DsApiId">API唯一编号</param>
        /// <param name="SiteID"></param>
        /// <returns></returns>
        public int AddUpdateDefine(int Id,string DsnewsId, string DsEName, string DsnewsTable, int DsType, string DsContent, string DsApiId, string SiteID,int flag)
        {
            string sql=null;
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@Id", SqlDbType.Int,4);
            param[0].Value = Id;
            param[1] = new SqlParameter("@DsnewsId", SqlDbType.NVarChar, 12);
            param[1].Value = DsnewsId;
            param[2] = new SqlParameter("@DsEName", SqlDbType.NVarChar, 50);
            param[2].Value = DsEName;
            param[3] = new SqlParameter("@DsnewsTable", SqlDbType.NVarChar, 50);
            param[3].Value = DsnewsTable;
            param[4] = new SqlParameter("@DsType", SqlDbType.TinyInt, 1);
            param[4].Value = DsType;
            param[5] = new SqlParameter("@DsContent", SqlDbType.NText, 16);
            param[5].Value = DsContent;
            param[6] = new SqlParameter("@DsApiId", SqlDbType.NVarChar, 30);
            param[6].Value = DsApiId;
            param[7] = new SqlParameter("@SiteID", SqlDbType.NVarChar, 12);
            param[7].Value = SiteID;
            if(flag==0)//添加
            {
                sql = "insert into " + Pre + "define_save(DsnewsId,DsEName,DsnewsTable,DsType,DsContent,DsApiId,SiteID) values(@DsnewsId,@DsEName,@DsnewsTable,@DsType,@DsContent,@DsApiId,@SiteID)";
            }
            else if (flag == 1)
            {
                sql = "update " + Pre + "define_save set DsContent=@DsContent where Id=@Id";
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, sql, param);
        }
    }
}